POI实现excel的导入导出-[未采用反射实现的版本]

2018-01-16

最近在做项目中遇到了使用POI导入导出excel的需求,所以在这里总结下，方便自己和朋友学习:
1.经典博客:
1.JavaWEB–POI之EXCEL操作、优化、封装详解系列（一）–概述与原理
2.公共POI导出Excel方法–java
3.Apache POI 实现对 Excel 文件读写
4.EasyPoi教程V1.0

2.原理:
原理如下图
这里写图片描述

3.代码:
POIController.java

package com.spring.boot.gp4zj.controller;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.spring.boot.gp4zj.service.POIService;
import cn.lz.cloud.common.service.ClazzNote;
import cn.lz.cloud.common.service.ServiceNote;
/**
 * 各种poi导入导出的请求控制模块;
 * 
 * @author:JunZhou
 * @Company:LongZheng
 * @Email:1769676159@qq.com
 * @2018年1月16日@下午4:28:49
 */
@RestController
@RequestMapping("/poi")
@ClazzNote(desc = "excel文件", resource = "input", modName = "poi")
public class POIController {
	@Autowired
	private POIService poiService;
	// 获取考勤明细的excel文件;
	@RequestMapping("/getAttenceDetailExcel")
	@ServiceNote(desc = "获取考勤明细的excel", auth = ServiceNote.AUTH.CHECK)
	public void getAttenceDetailExcel(HttpServletResponse response) {
		poiService.getAttenceDetailExcel(response);
	}
	
	// 读取考勤明细的excel文件;
	@RequestMapping("/readAttenceDetailData")
	@ServiceNote(desc = "获取考勤明细的excel", auth = ServiceNote.AUTH.CHECK)
	public void readAttenceDetailData() {
		
	}
}

POIServiceImpl.java

package com.spring.boot.gp4zj.service.impl;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.spring.boot.gp4zj.model.AttenceDetail;
import com.spring.boot.gp4zj.model.SimulationVO;
import com.spring.boot.gp4zj.service.AttenceDetailService;
import com.spring.boot.gp4zj.service.POIService;
import com.spring.boot.gp4zj.util.POIUtils;
import cn.lz.cloud.common.service.ResList;
/**
 * 获取excel的业务逻辑模块;
 * @author:JunZhou
 * @Company:LongZheng
 * @Email:1769676159@qq.com
 * @2018年1月16日@下午4:47:09
 */
@Service
public class POIServiceImpl implements POIService {
	@Autowired
	private AttenceDetailService attenceDetailService;
	public void getAttenceDetailExcel(HttpServletResponse response) {
		// TODO Auto-generated method stub
		ResList<AttenceDetail>  list = attenceDetailService.selectAll(SimulationVO.getADSimulationVo());
		List<AttenceDetail> excelData = list.getList();
		HSSFWorkbook workbook = POIUtils.createExcel(excelData);
		OutputStream out = null;
		if (workbook != null) {
			try {
				String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
				String headStr = "attachment; filename=\"" + fileName + "\"";
				response.setContentType("APPLICATION/OCTET-STREAM");
				response.setHeader("Content-Disposition", headStr);
				out = response.getOutputStream();
				workbook.write(out);
				out.flush();
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				try {
					out.flush();
					out.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
	}
}

POIUtils.java

package com.spring.boot.gp4zj.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.spring.boot.gp4zj.model.AttenceDetail;
/**
 * Excel操作相关的工具类;
 * 
 * @author:JunZhou
 * @Company:LongZheng
 * @Email:1769676159@qq.com
 * @2018年1月16日@下午3:19:25
 */
public class POIUtils {
	public static void main(String[] args) {
		List<AttenceDetail> list = readExcel();
		/*for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i).toString());
		}*/
		System.out.println(list.get(0).toString());
	}
	/**
	 * 创建Excel
	 * 
	 * @param list
	 *            数据
	 */
	public static HSSFWorkbook createExcel(List<AttenceDetail> list) {
		// 创建一个Excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建一个工作表
		HSSFSheet sheet = workbook.createSheet("考勤明细表");
		// 添加表头行
		HSSFRow hssfRow = sheet.createRow(0);
		// 设置单元格格式居中
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 添加表头内容
		// -------第一行第0-2列---------
		HSSFCell headCell = hssfRow.createCell(0);
		headCell.setCellValue("考勤日期");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(1);
		headCell.setCellValue("考勤名称");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(2);
		headCell.setCellValue("员工编码");
		headCell.setCellStyle(cellStyle);
		// -----------------------
		headCell = hssfRow.createCell(3);
		headCell.setCellValue("出勤时长");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(4);
		headCell.setCellValue("加班时长");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(5);
		headCell.setCellValue("合计出勤时长");
		headCell.setCellStyle(cellStyle);
		// -----------------------
		headCell = hssfRow.createCell(6);
		headCell.setCellValue("报工单位");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(7);
		headCell.setCellValue("结算单位");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(8);
		headCell.setCellValue("创建人");
		headCell.setCellStyle(cellStyle);
		// -----------------------
		headCell = hssfRow.createCell(9);
		headCell.setCellValue("出勤人天");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(10);
		headCell.setCellValue("加班人天");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(11);
		headCell.setCellValue("合计人天");
		headCell.setCellStyle(cellStyle);
		// -----------------------
		headCell = hssfRow.createCell(12);
		headCell.setCellValue("是否删除汇总表");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(13);
		headCell.setCellValue("是否确认");
		headCell.setCellStyle(cellStyle);
		headCell = hssfRow.createCell(14);
		headCell.setCellValue("创建时间");
		headCell.setCellStyle(cellStyle);
		// -----------------------
		headCell = hssfRow.createCell(15);
		headCell.setCellValue("修改时间");
		headCell.setCellStyle(cellStyle);
		// 添加数据内容
		for (int i = 0; i < list.size(); i++) {
			hssfRow = sheet.createRow((int) i + 1);
			AttenceDetail attenceDetail = list.get(i);
			// 创建单元格，并设置值
			
			HSSFCell cell = hssfRow.createCell(0);
			cell.setCellValue(attenceDetail.getAttencedTime());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(1);
			cell.setCellValue(attenceDetail.getAttencerName());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(2);
			cell.setCellValue(attenceDetail.getAttencerCode());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(3);
			cell.setCellValue(attenceDetail.getWorkTimeLen());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(4);
			cell.setCellValue(attenceDetail.getExtraTimeLen());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(5);
			cell.setCellValue(attenceDetail.getTotalTimeLen());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(6);
			cell.setCellValue(attenceDetail.getApplyCompany());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(7);
			cell.setCellValue(attenceDetail.getPayCompany());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(8);
			cell.setCellValue(attenceDetail.getCreator());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(9);
			cell.setCellValue(attenceDetail.getWorkDayNum());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(10);
			cell.setCellValue(attenceDetail.getExtraDayNum());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(11);
			cell.setCellValue(attenceDetail.getTotalDayNum());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(12);
			cell.setCellValue(attenceDetail.getIsDelGather());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(13);
			cell.setCellValue(attenceDetail.getIsConfirmed());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(14);
			cell.setCellValue(attenceDetail.getCreateTime());
			cell.setCellStyle(cellStyle);
			cell = hssfRow.createCell(15);
			cell.setCellValue(attenceDetail.getEditTime());
			cell.setCellStyle(cellStyle);
		}
		/*// 保存Excel文件
		try {
			OutputStream outputStream = new FileOutputStream("D:/students.xls");
			workbook.write(outputStream);
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}*/
		return workbook;
	}
	
	/**
	 * 接收上传的excel并读取其中的信息;
	 */
	public static List<AttenceDetail> readExcel(){
        List<AttenceDetail> list = new ArrayList<AttenceDetail>();
        HSSFWorkbook workbook = null;
        try {
            // 读取Excel文件
            InputStream inputStream = new FileInputStream("D:/Excel-095968073.xls");
            workbook = new HSSFWorkbook(inputStream);
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 循环工作表
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                // 将单元格中的内容存入集合
                AttenceDetail attenceDetail = new AttenceDetail();
                HSSFCell cell = hssfRow.getCell(0);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setAttencedTime(cell.getStringCellValue());
                cell = hssfRow.getCell(1);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setAttencerName(cell.getStringCellValue());
                cell = hssfRow.getCell(2);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setAttencerCode(cell.getStringCellValue());
                //-------------------------------
                cell = hssfRow.getCell(3);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setWorkTimeLen(cell.getStringCellValue());
                
                cell = hssfRow.getCell(4);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setExtraTimeLen(cell.getStringCellValue());
                
                cell = hssfRow.getCell(5);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setTotalTimeLen(cell.getStringCellValue());
                
              //-------------------------------
                cell = hssfRow.getCell(6);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setApplyCompany(cell.getStringCellValue());
                
                cell = hssfRow.getCell(7);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setPayCompany(cell.getStringCellValue());
                
              //-------------------------------
                cell = hssfRow.getCell(8);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setCreator(cell.getStringCellValue());
               
                cell = hssfRow.getCell(9);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setWorkDayNum(cell.getStringCellValue());
                
                cell = hssfRow.getCell(10);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setExtraDayNum(cell.getStringCellValue());
              
                //-------------------------------
                cell = hssfRow.getCell(11);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setTotalDayNum(cell.getStringCellValue());
                
                cell = hssfRow.getCell(12);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setIsDelGather(cell.getStringCellValue());
                
                cell = hssfRow.getCell(13);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setIsConfirmed(cell.getStringCellValue());
                
                cell = hssfRow.getCell(14);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setCreateTime(cell.getStringCellValue());
              //-------------------------------
                cell = hssfRow.getCell(15);
                if (cell == null) {
                    continue;
                }
                attenceDetail.setEditTime(cell.getStringCellValue());
                list.add(attenceDetail);
            }
        }
        return list;
	}
}

这部份的代码由于没有采用反射实现，效率较高，但是代码冗余和灵活性不高，所以接下来将采用反射实现.。